package com.gjzt.business.task.service;

import java.util.Date;
import java.util.List;

import com.gjzt.business.media.inter.MediaFileMapper;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.eastdigit.servlet.ReqBean;
import com.eastdigit.system.model.BusinessException;
import com.eastdigit.system.model.SqlBuilder;
import com.eastdigit.system.service.BaseService;
import com.eastdigit.util.DateUtil;
import com.eastdigit.util.StringUtil;
import com.gjzt.business.media.service.MediaFileService;
import com.gjzt.business.task.inter.CheckResultMapper;
import com.gjzt.business.task.inter.CheckTaskMapper;
import com.gjzt.business.task.inter.TaskItemMapper;
import com.gjzt.business.task.inter.TaskTemplateMapper;
import com.gjzt.business.task.model.CheckResult;
import com.gjzt.business.task.model.CheckTask;
import com.gjzt.business.task.model.TaskItem;
import com.gjzt.business.task.model.TaskTemplate;
import com.gjzt.business.utils.Constant;

@Service
public class CheckTaskService extends BaseService {

    @Autowired
    private CheckTaskMapper checkTaskMapper;

    @Autowired
    private TaskTemplateMapper taskTemplateMapper;

    @Autowired
    private TaskItemMapper taskItemMapper;

    @Autowired
    private CheckResultMapper checkResultMapper;

    @Autowired
    private MediaFileService mediaFileService;

    /**
     * 查询
     * 
     * @param reqBean
     * @return
     */
    public JSONObject findCheckTask(ReqBean reqBean) {
        String sql = "select a.*,"
                + "((SELECT COUNT(1) FROM AD_CHECK_TASKITEM WHERE TASK_ID = a.TASK_ID AND STATUS = '"
                + Constant.CHECK_TASK_ITEM_STATUS_COMPLETE
                + "') / (SELECT COUNT(1) FROM AD_CHECK_TASKITEM WHERE TASK_ID = a.TASK_ID)) percent "
                + "from AD_CHECK_TASK a where 1=1 ";
        SqlBuilder sqlBuilder = new SqlBuilder(sql);
        String quickSearch = reqBean.getInfo("quickSearch");
        if (Constant.QUICK_SEARCH_TRUE.equals(quickSearch)) {
            sqlBuilder.addWhereLike2("a.TASK_TITLE", reqBean.getInfo("quickSearchName"));
        } else {
            sqlBuilder.addWhereLike2("a.TASK_TITLE", reqBean.getInfo("taskTitle"))
                    .addWhere("a.TASK_TYPE", reqBean.getInfo("taskType"))
                    .addWhere("a.STATUS", reqBean.getInfo("status"));
        }
        sqlBuilder.orderBy(" order by a.MAKE_TIME DESC ");
        return this.queryTable(sqlBuilder, reqBean.getPageSize(), reqBean.getPageIndex());
    }

    /**
     * 新增
     * 
     * @param reqBean
     */
    public void addCheckTask(ReqBean reqBean) {
        CheckTask checkTask = getCheckTask(reqBean);
        checkTask.setStatus(CheckTask.CHECK_TASK_STATUS_NOT_PUBLISH); // 未发布
        checkTask.setMakeTime(checkTask.getModifyTime());

        checkTask.setTaskId(intIncrementId("AD_CHECK_TASK", "TASK_ID"));
        checkTaskMapper.insert(checkTask);

        // 插入检查项关联数据

        // String templateIdsStr = reqBean.getInfo("templateIds");
        // if (StringUtils.isBlank(templateIdsStr) ||
        // "[]".equals(templateIdsStr)) {
        // throw new BusinessException("未配置检查项!");
        // }
        // JSONArray templateIds = JSONArray.fromObject(templateIdsStr);
        //
        // JSONArray array = new JSONArray();
        // if (Constant.CHECK_TASK_TYPE_BUS.equals(checkTask.getTaskType())) {
        // String sql = "select a.BUS_ID from MEDIA_BUS a limit 0,30";
        // array = query(sql);
        // } else if
        // (Constant.CHECK_TASK_TYPE_STOP.equals(checkTask.getTaskType())) {
        // String sql = "select a.STOP_ID from MEDIA_STOP a";
        // array = query(sql);
        // } else if
        // (Constant.CHECK_TASK_TYPE_ADVERTISEMENT.equals(checkTask.getTaskType()))
        // {
        // String sql =
        // "SELECT a.*,d.ID,d.RESOURCE_ID FROM `AD_PUBLISH_BILL` a LEFT JOIN AD_PUBLISH_BILL_DETAIL d "
        // +
        // "ON d.BILL_ID = a.BILL_ID where curdate() BETWEEN a.PUBLISH_TIME and a.PUBLISH_OFF_TIME";
        // array = query(sql);
        // }
        //
        // for (int i = 0; i < array.size(); i++) {
        // JSONObject dataJson = array.getJSONObject(i);
        // // 初始化任务明细
        // TaskItem item = new TaskItem();
        // item.setTaskId(checkTask.getTaskId());
        // if (Constant.CHECK_TASK_TYPE_BUS.equals(checkTask.getTaskType())) {
        // item.setItemId(dataJson.getString("busId"));
        // } else if
        // (Constant.CHECK_TASK_TYPE_STOP.equals(checkTask.getTaskType())) {
        // item.setItemId(dataJson.getString("stopId"));
        // } else if
        // (Constant.CHECK_TASK_TYPE_ADVERTISEMENT.equals(checkTask.getTaskType()))
        // {
        // item.setItemId(dataJson.getString("billId") + "@" +
        // dataJson.getString("resourceId"));
        // item.setPublishOffTime(DateUtil.parseDateTime(dataJson.getString("publishOffTime")));
        // item.setPublishTime(DateUtil.parseDateTime(dataJson.getString("publishTime")));
        // }
        // item.setModifyTime(new Date());
        // item.setMakeTime(new Date());
        // item.setOperator(reqBean.getUserId());
        // item.setStatus(Constant.CHECK_TASK_ITEM_STATUS_NOT_COMPLETE);
        // taskItemMapper.insert(item);
        // }
        //
        // if (templateIds != null && templateIds.size() > 0) {
        // for (int i = 0; i < templateIds.size(); i++) {
        // JSONObject json = templateIds.getJSONObject(i);
        // TaskTemplate taskTemplate = new TaskTemplate();
        // taskTemplate.setTaskId(checkTask.getTaskId());
        // taskTemplate.setTemplateId(json.getString("value"));
        // taskTemplateMapper.insert(taskTemplate);
        //
        // // 初始化检查结果
        // for (int j = 0; j < array.size(); j++) {
        // JSONObject dataJson = array.getJSONObject(j);
        // // 初始化检查结果数据
        // CheckResult result = new CheckResult();
        // result.setTemplateId(taskTemplate.getTemplateId());
        // result.setTaskId(checkTask.getTaskId());
        // if (Constant.CHECK_TASK_TYPE_BUS.equals(checkTask.getTaskType())) {
        // result.setItemId(dataJson.getString("busId"));
        // } else if
        // (Constant.CHECK_TASK_TYPE_STOP.equals(checkTask.getTaskType())) {
        // result.setItemId(dataJson.getString("stopId"));
        // } else if
        // (Constant.CHECK_TASK_TYPE_ADVERTISEMENT.equals(checkTask.getTaskType()))
        // {
        // result.setItemId(dataJson.getString("billId") + "@" +
        // dataJson.getString("resourceId"));
        // }
        // result.setOperator(reqBean.getUserId());
        // result.setMakeTime(new Date());
        // result.setModifyTime(new Date());
        // checkResultMapper.insert(result);
        // }
        // }
        // }
    }

    public JSONObject adCheckCheckTaskQuery(ReqBean reqBean) {

        String sql = "SELECT a.*,t.MEDIA_NAME,d.ID,d.RESOURCE_ID,c.CUSTOMER_NAME,CONCAT(a.BILL_ID,'@',d.RESOURCE_ID) itemId FROM `AD_PUBLISH_BILL` a "
                + "LEFT JOIN AD_PUBLISH_BILL_DETAIL d ON d.BILL_ID = a.BILL_ID "
                + "LEFT JOIN AD_CUSTOMER c ON c.CUSTOMER_ID = a.CUSTOMER_ID "
                + "LEFT JOIN MEDIA_RESOURCE r ON r.RESOURCE_ID = d.RESOURCE_ID "
                + "LEFT JOIN MEDIA_TYPE t on t.MEDIA_ID = r.MEDIA_TYPE "
                + "where curdate() BETWEEN a.PUBLISH_TIME and a.PUBLISH_OFF_TIME ";

        SqlBuilder sqlBuilder = new SqlBuilder(sql).addWhereLike2("a.BILL_NO", reqBean.getInfo("billNo"))
                .addWhereLike2("c.CUSTOMER_NAME", reqBean.getInfo("customerName"));

        return queryTable(sqlBuilder, reqBean.getPageSize(), reqBean.getPageIndex());

    }

    public void saveSelectItem(ReqBean reqBean) {

        String taskType = reqBean.getInfo("taskType");

        String itemStr = reqBean.getInfo("checkItem");

        String taskId = reqBean.getInfo("taskId");

        if (StringUtils.isBlank(taskType) || StringUtils.isBlank(taskId) || StringUtils.isBlank(itemStr)
                || "[]".equals(itemStr)) {
            throw new BusinessException("非法请求!");
        }

        JSONArray itemList = JSONArray.fromObject(itemStr);

        if (itemList != null && itemList.size() > 0) {

            // 先删除已有数据
            taskItemMapper.deleteByTaskId(taskId);
            // 插入
            for (int i = 0; i < itemList.size(); i++) {
                JSONObject json = itemList.getJSONObject(i);
                // 初始化任务明细
                TaskItem item = new TaskItem();
                item.setTaskId(taskId);
                // if (Constant.CHECK_TASK_TYPE_BUS.equals(taskType)) {
                // item.setItemId(json.getString("itemId"));
                // } else if (Constant.CHECK_TASK_TYPE_STOP.equals(taskType)) {
                // item.setItemId(json.getString("stopId"));
                // } else if
                // (Constant.CHECK_TASK_TYPE_ADVERTISEMENT.equals(taskType)) {
                // item.setItemId(json.getString("billId") + "@" +
                // json.getString("resourceId"));
                // item.setPublishOffTime(DateUtil.parseDateTime(json.getString("publishOffTime")));
                // item.setPublishTime(DateUtil.parseDateTime(json.getString("publishTime")));
                // }
                item.setItemId(json.getString("itemId"));
                if (Constant.CHECK_TASK_TYPE_ADVERTISEMENT.equals(taskType)) {
                    item.setPublishOffTime(DateUtil.parseDateTime(json.getString("publishOffTime")));
                    item.setPublishTime(DateUtil.parseDateTime(json.getString("publishTime")));
                }
                item.setModifyTime(new Date());
                item.setMakeTime(new Date());
                item.setOperator(reqBean.getUserId());
                item.setStatus(Constant.CHECK_TASK_ITEM_STATUS_NOT_COMPLETE);
                taskItemMapper.insert(item);
            }
        }

    }

    public void saveTemplates(ReqBean reqBean) {
        String templateIds = reqBean.getInfo("templateIds");
        String hasSelectItemListStr = reqBean.getInfo("hasSelectItemList");
        String taskId = reqBean.getInfo("taskId");

        if (StringUtils.isBlank(taskId) || StringUtils.isBlank(templateIds) || "[]".equals(templateIds)
                || StringUtils.isBlank(hasSelectItemListStr) || "[]".equals(hasSelectItemListStr)) {
            throw new BusinessException("非法请求!");
        }

        JSONArray templateList = JSONArray.fromObject(templateIds);
        JSONArray hasSelectItemList = JSONArray.fromObject(hasSelectItemListStr);
        taskTemplateMapper.deleteByTaskId(taskId);
        checkResultMapper.deleteByTaskId(taskId);
        for (int i = 0; i < templateList.size(); i++) {
            JSONObject json = templateList.getJSONObject(i);
            TaskTemplate taskTemplate = new TaskTemplate();
            taskTemplate.setTaskId(taskId);
            taskTemplate.setTemplateId(json.getString("value"));
            taskTemplateMapper.insert(taskTemplate);

            //  初始化检查结果
            for (int j = 0; j < hasSelectItemList.size(); j++) {
                JSONObject dataJson = hasSelectItemList.getJSONObject(j);
                // 初始化检查结果数据
                CheckResult result = new CheckResult();
                result.setTemplateId(taskTemplate.getTemplateId());
                result.setTaskId(taskId);
                result.setItemId(dataJson.getString("itemId"));
                result.setOperator(reqBean.getUserId());
                result.setMakeTime(new Date());
                result.setModifyTime(new Date());
                checkResultMapper.insert(result);
            }
        }
    }

    public void checkItemDelete(ReqBean reqBean) {

        String taskId = reqBean.getInfo("taskId");
        String itemId = reqBean.getInfo("itemId");

        if (StringUtils.isBlank(taskId) || StringUtils.isBlank(itemId)) {
            throw new BusinessException("非法请求!");
        }

        taskItemMapper.deleteByPrimaryKey(taskId, itemId);
    }

    // private void insertTaskDetail(String taskId, String taskType, String
    // userId) {
    // String sql = "";
    // if (Constant.CHECK_TASK_TYPE_BUS.equals(taskType)) { // 车辆
    // sql =
    // "INSERT INTO AD_CHECK_TASKITEM (TASK_ID,ITEM_ID,OPERATOR,MAKE_TIME,MODIFY_TIME) SELECT '"
    // + taskId
    // + "',BUS_ID,'" + userId + "', '" + DateUtil.formatDateTime(new Date()) +
    // "', '"
    // + DateUtil.formatDateTime(new Date()) + "' FROM MEDIA_BUS LIMIT 0,10";
    // } else if (Constant.CHECK_TASK_TYPE_STOP.equals(taskType)) {
    // sql =
    // "INSERT INTO AD_CHECK_TASKITEM (TASK_ID,ITEM_ID,OPERATOR,MAKE_TIME,MODIFY_TIME) SELECT '"
    // + taskId
    // + "',STOP_ID,'" + userId + "', '" + DateUtil.formatDateTime(new Date()) +
    // "', '"
    // + DateUtil.formatDateTime(new Date()) + "' FROM MEDIA_STOP LIMIT 0,10";
    // }
    // jdbcTemplate.update(sql);

    // }

    /**
     * 修改
     * 
     * @param reqBean
     */
    public void updateCheckTask(ReqBean reqBean) {
        String taskId = reqBean.getInfo("taskId");
        if (StringUtil.isEmpty(taskId)) {
            throw new BusinessException("获取主键失败");
        }
        CheckTask checkTask = getCheckTask(reqBean);
        checkTaskMapper.updateByPrimaryKeySelective(checkTask);

        // 操作检查项关联数据
        // 先删除在插入
        // taskTemplateMapper.deleteByTaskId(taskId);
        //
        // // 插入
        // String templateIdsStr = reqBean.getInfo("templateIds");
        // if (StringUtils.isBlank(templateIdsStr) ||
        // "[]".equals(templateIdsStr)) {
        // throw new BusinessException("未配置检查项!");
        // }
        // JSONArray templateIds = JSONArray.fromObject(templateIdsStr);
        // if (templateIds != null && templateIds.size() > 0) {
        // for (int i = 0; i < templateIds.size(); i++) {
        // JSONObject json = templateIds.getJSONObject(i);
        // TaskTemplate taskTemplate = new TaskTemplate();
        // taskTemplate.setTaskId(checkTask.getTaskId());
        // taskTemplate.setTemplateId(json.getString("value"));
        // taskTemplateMapper.insert(taskTemplate);
        // }
        // }

    }

    /**
     * 删除
     * 
     * @param reqBean
     */
    public void removeCheckTask(ReqBean reqBean) {
        String taskId = reqBean.getInfo("taskId");
        if (StringUtil.isEmpty(taskId)) {
            throw new BusinessException("获取主键失败");
        }
        checkTaskMapper.deleteByPrimaryKey(taskId);

        // 删除检查项数据
        taskTemplateMapper.deleteByTaskId(taskId);

        // 删除清单明细数据
        taskItemMapper.deleteByTaskId(taskId);

        // 删除检查结果数据
        checkResultMapper.deleteByTaskId(taskId);

    }

    /**
     * 发布
     * 
     * @param reqBean
     */
    public void releaseCheckTask(ReqBean reqBean) {
        String taskId = reqBean.getInfo("taskId");
        if (StringUtil.isEmpty(taskId)) {
            throw new BusinessException("获取主键失败");
        }
        CheckTask checkTask = getCheckTask(reqBean);
        checkTask.setStatus(CheckTask.CHECK_TASK_STATUS_PUBLISHED);
        checkTask.setPublishTime(new Date());
        checkTaskMapper.updateByPrimaryKeySelective(checkTask);
    }

    /**
     * 完结
     * 
     * @param reqBean
     */
    public void finishCheckTask(ReqBean reqBean) {
        String taskId = reqBean.getInfo("taskId");
        if (StringUtil.isEmpty(taskId)) {
            throw new BusinessException("获取主键失败");
        }
        CheckTask checkTask = getCheckTask(reqBean);
        checkTask.setStatus(CheckTask.CHECK_TASK_STATUS_COMPLETE);
        checkTask.setCompleteTime(new Date());
        checkTaskMapper.updateByPrimaryKeySelective(checkTask);
    }

    /**
     * 从表单获取对象
     * 
     * @param reqBean
     * @return
     */
    private CheckTask getCheckTask(ReqBean reqBean) {
        CheckTask checkTask = new CheckTask();

        checkTask.setTaskId(reqBean.getInfo("taskId"));
        checkTask.setTaskTitle(reqBean.getInfo("taskTitle"));
        checkTask.setTaskType(reqBean.getInfo("taskType"));
        checkTask.setOperator(reqBean.getUserId());
        checkTask.setStatus(reqBean.getInfo("status"));
        checkTask.setCompleteTime(DateUtil.parseDateTime(reqBean.getInfo("completeTime")));
        checkTask.setPublishTime(DateUtil.parseDateTime(reqBean.getInfo("publishTime")));
        checkTask.setMakeTime(DateUtil.parseDateTime(reqBean.getInfo("makeTime")));
        checkTask.setModifyTime(new Date());
        checkTask.setRemark(reqBean.getInfo("remark"));

        return checkTask;
    }

    public JSONArray queryTemplates(ReqBean reqBean) {

        String taskId = reqBean.getInfo("taskId");
        if (StringUtils.isBlank(taskId)) {
            throw new BusinessException("缺少参数!");
        }

        String sql = "select tt.TASK_ID,ct.* FROM AD_CHECK_TASKTEMPLATE tt LEFT JOIN AD_CHECK_TEMPLATE ct "
                + "ON ct.TEMPLATE_ID = tt.TEMPLATE_ID where tt.TASK_ID = '" + taskId + "'";

        return query(sql);
    }

    public JSONObject dynamicData(ReqBean reqBean) {
        String taskType = reqBean.getInfo("taskType");
        String quickSearch = reqBean.getInfo("quickSearch");
        String taskId = reqBean.getInfo("taskId");

        if (StringUtils.isBlank(taskType) || StringUtils.isBlank(taskId)) {
            throw new BusinessException("缺少参数!");
        }

        String sql = "";
        SqlBuilder sqlBuilder = null;

        if (Constant.CHECK_TASK_TYPE_BUS.equals(taskType)) {
            sql = "SELECT li.*,b.VEHICLE_NO,b.PLATE_NO,b.LINE_NO,b.COMPANY_NAME,u.USER_NAME assigneeName FROM AD_CHECK_TASKITEM li "
                    + "LEFT JOIN MEDIA_BUS b ON b.BUS_ID = li.ITEM_ID "
                    + "LEFT JOIN SYS_USER u ON u.USER_ID = li.ASSIGNEE " + "where li.TASK_ID = '" + taskId + "'";

            if (Constant.QUICK_SEARCH_TRUE.equals(quickSearch)) {
                sqlBuilder = new SqlBuilder(sql).addWhereLike2("tl.TASK_TITLE", reqBean.getInfo("quickSearchName"))
                        .orderBy(" order by b.PLATE_NO");
            } else {
                sqlBuilder = new SqlBuilder(sql).addWhere("b.VEHICLE_NO", reqBean.getInfo("vehicleNo"))
                        .addWhere("b.PLATE_NO", reqBean.getInfo("plateNo"))
                        .addWhere("b.LINE_NO", reqBean.getInfo("lineNo"))
                        .addWhereLike2("b.COMPANY_ID", reqBean.getInfo("companyId")).orderBy(" order by b.PLATE_NO");
            }

        } else if (Constant.CHECK_TASK_TYPE_STOP.equals(taskType)) {
            sql = "SELECT li.*,s.STOP_NAME,s.ROAD_NAME,s.DIRECTION,u.USER_NAME assigneeName FROM AD_CHECK_TASKITEM li "
                    + "LEFT JOIN MEDIA_STOP s ON s.STOP_ID = li.ITEM_ID "
                    + "LEFT JOIN SYS_USER u ON u.USER_ID = li.ASSIGNEE " + "where li.TASK_ID = '" + taskId + "' ";
            sqlBuilder = new SqlBuilder(sql).addWhereLike2("s.STOP_NAME", reqBean.getInfo("stopName"))
                    .addWhere("s.ROAD_NAME", reqBean.getInfo("roadName"))
                    .addWhere("s.DIRECTION", reqBean.getInfo("direction")).orderBy(" order by s.STOP_NAME");
        } else if (Constant.CHECK_TASK_TYPE_ADVERTISEMENT.equals(taskType)) {
            sql = "SELECT li.*,r.RESOURCE_ID,r.RESOURCE_CODE,r.RESOURCE_NAME,r.MEDIA_TYPE,t.MEDIA_NAME,u.USER_NAME assigneeName,"
                    + "pb.BILL_NO,pb.CONTENT publishContent,c.CUSTOMER_NAME,CONCAT(li.PUBLISH_TIME,' 至 ',li.PUBLISH_OFF_TIME) publishTime "
                    + "FROM AD_CHECK_TASKITEM li LEFT JOIN MEDIA_RESOURCE r ON "
                    + "r.RESOURCE_ID = substring_index(li.ITEM_ID,'@',-1) "
                    + "LEFT JOIN SYS_USER u ON u.USER_ID = li.ASSIGNEE "
                    + "LEFT JOIN MEDIA_TYPE t ON t.MEDIA_ID = r.MEDIA_TYPE "
                    + "LEFT JOIN AD_PUBLISH_BILL pb ON pb.BILL_ID = SUBSTRING_INDEX(li.ITEM_ID,'@',1) "
                    + "LEFT JOIN AD_CUSTOMER c ON c.CUSTOMER_ID = pb.CUSTOMER_ID "
                    + "where li.TASK_ID = '"
                    + taskId
                    + "'";
            sqlBuilder = new SqlBuilder(sql).addWhere("r.RESOURCE_CODE", reqBean.getInfo("resourceCode"))
                    .addWhere("r.MEDIA_TYPE", reqBean.getInfo("mediaType"))
                    .addWhereLike2("pb.BILL_NO", reqBean.getInfo("billNo")).orderBy(" ORDER BY r.RESOURCE_ID");
        }
        JSONObject returnJson = queryTable(sqlBuilder, reqBean.getPageSize(), reqBean.getPageIndex());
        JSONArray rows = returnJson.getJSONArray("rows");
        if (rows != null && rows.size() > 0) {
            for (int i = 0; i < rows.size(); i++) {
                JSONObject data = rows.getJSONObject(i);
                List<CheckResult> resultList = checkResultMapper.selectByTaskIdAndItemId(data.getString("taskId"),
                        data.getString("itemId"));
                for (int j = 0; j < resultList.size(); j++) {
                    CheckResult result = resultList.get(j);
                    data.put("templateId" + result.getTemplateId(), result.getTemplateValue());
                }
            }
        }

        return returnJson;
    }

    public JSONArray dynamicData4ReShow(ReqBean reqBean) {

        String taskType = reqBean.getInfo("taskType");
        String taskId = reqBean.getInfo("taskId");

        if (StringUtils.isBlank(taskType) || StringUtils.isBlank(taskId)) {
            throw new BusinessException("缺少参数!");
        }

        String sql = "";

        if (Constant.CHECK_TASK_TYPE_BUS.equals(taskType)) {
            sql = "SELECT li.*,b.VEHICLE_NO,b.PLATE_NO,b.LINE_NO,b.COMPANY_NAME,u.USER_NAME assigneeName FROM AD_CHECK_TASKITEM li "
                    + "LEFT JOIN MEDIA_BUS b ON b.BUS_ID = li.ITEM_ID "
                    + "LEFT JOIN SYS_USER u ON u.USER_ID = li.ASSIGNEE "
                    + "where li.TASK_ID = '"
                    + taskId
                    + "' order by b.PLATE_NO";

        } else if (Constant.CHECK_TASK_TYPE_STOP.equals(taskType)) {
            sql = "SELECT li.*,s.STOP_NAME,s.ROAD_NAME,s.DIRECTION,u.USER_NAME assigneeName FROM AD_CHECK_TASKITEM li "
                    + "LEFT JOIN MEDIA_STOP s ON s.STOP_ID = li.ITEM_ID "
                    + "LEFT JOIN SYS_USER u ON u.USER_ID = li.ASSIGNEE " + "where li.TASK_ID = '" + taskId + "'";
        } else if (Constant.CHECK_TASK_TYPE_ADVERTISEMENT.equals(taskType)) {
            sql = "SELECT li.*,r.RESOURCE_ID,r.RESOURCE_CODE,r.RESOURCE_NAME,r.MEDIA_TYPE,t.MEDIA_NAME,u.USER_NAME assigneeName,"
                    + "pb.BILL_NO,pb.CONTENT publishContent,c.CUSTOMER_NAME,CONCAT(li.PUBLISH_TIME,' 至 ',li.PUBLISH_OFF_TIME) publishTime "
                    + "FROM AD_CHECK_TASKITEM li LEFT JOIN MEDIA_RESOURCE r ON "
                    + "r.RESOURCE_ID = substring_index(li.ITEM_ID,'@',-1) "
                    + "LEFT JOIN SYS_USER u ON u.USER_ID = li.ASSIGNEE "
                    + "LEFT JOIN MEDIA_TYPE t ON t.MEDIA_ID = r.MEDIA_TYPE "
                    + "LEFT JOIN AD_PUBLISH_BILL pb ON pb.BILL_ID = SUBSTRING_INDEX(li.ITEM_ID,'@',1) "
                    + "LEFT JOIN AD_CUSTOMER c ON c.CUSTOMER_ID = pb.CUSTOMER_ID "
                    + "where li.TASK_ID = '"
                    + taskId
                    + "'";
        }

        return query(sql);
    }

    public JSONArray dynamicHead(ReqBean reqBean) {
        String taskType = reqBean.getInfo("taskType");
        String taskId = reqBean.getInfo("taskId");

        if (StringUtils.isBlank(taskType) || StringUtils.isBlank(taskId)) {
            throw new BusinessException("缺少参数!");
        }

        // 检查项表头数据
        String sql = "select a.TASK_ID, t.TEMPLATE_ID,t.TEMPLATE_NAME FROM AD_CHECK_TASKTEMPLATE a LEFT JOIN "
                + "AD_CHECK_TEMPLATE t ON t.TEMPLATE_ID = a.TEMPLATE_ID where a.TASK_ID = '" + taskId + "'";
        JSONArray templateArr = query(sql);

        JSONArray returnArr = new JSONArray();
        JSONObject json = new JSONObject();
        json.put("col", "_index");
        json.put("name", "NO.");
        returnArr.add(json);

        if (Constant.CHECK_TASK_TYPE_BUS.equals(taskType)) { // 车辆

            json.put("col", "vehicleNo");
            json.put("name", "自编号");
            returnArr.add(json);

            json.put("col", "plateNo");
            json.put("name", "车牌号");
            returnArr.add(json);

            json.put("col", "lineNo");
            json.put("name", "线路号");
            returnArr.add(json);

            json.put("col", "companyName");
            json.put("name", "经营单位");
            returnArr.add(json);
        } else if (Constant.CHECK_TASK_TYPE_STOP.equals(taskType)) { // 站点
            json.put("col", "stopName");
            json.put("name", "站点名称");
            returnArr.add(json);

            json.put("col", "roadName");
            json.put("name", "街道");
            returnArr.add(json);

            json.put("col", "direction");
            json.put("name", "行向");
            returnArr.add(json);
        } else if (Constant.CHECK_TASK_TYPE_ADVERTISEMENT.equals(taskType)) {

            json.put("col", "billNo");
            json.put("name", "上刊单编号");
            returnArr.add(json);

            json.put("col", "publishContent");
            json.put("name", "广告内容");
            returnArr.add(json);

            json.put("col", "publishTime");
            json.put("name", "上刊期限");
            returnArr.add(json);

            json.put("col", "customerName");
            json.put("name", "经营单位");
            returnArr.add(json);

            json.put("col", "resourceCode");
            json.put("name", "资源编号");
            returnArr.add(json);

            json.put("col", "mediaName");
            json.put("name", "媒体类型");
            returnArr.add(json);
        }

        json.put("col", "assigneeName");
        json.put("name", "检查人");
        returnArr.add(json);

        json.put("col", "status | code : 'CHECK_TASK_ITEM_STATUS'");
        json.put("name", "状态");
        returnArr.add(json);

        json.put("col", "completeTime");
        json.put("name", "完成时间");
        returnArr.add(json);

        if (templateArr != null && templateArr.size() > 0) {
            for (int i = 0; i < templateArr.size(); i++) {
                JSONObject data = templateArr.getJSONObject(i);
                json.put("col", "templateId" + data.getString("templateId"));
                json.put("name", data.getString("templateName"));
                returnArr.add(json);
            }
        }
        json.put("col", "");
        json.put("name", "操作");
        returnArr.add(json);
        return returnArr;
    }

    public JSONArray checkResult(ReqBean reqBean) {
        String taskId = reqBean.getInfo("taskId");
        String itemId = reqBean.getInfo("itemId");
        if (StringUtils.isBlank(taskId) || StringUtils.isBlank(itemId)) {
            throw new BusinessException("缺少参数!");
        }

        String sql = "SELECT a.*,t.TEMPLATE_NAME,i.`COMMENT`,i.PUBLISH_OFF_TIME,i.PUBLISH_TIME FROM AD_CHECK_RESULT a "
                + "LEFT JOIN AD_CHECK_TASKITEM i on i.TASK_ID = a.TASK_ID AND i.ITEM_ID = a.ITEM_ID "
                + "LEFT JOIN AD_CHECK_TEMPLATE t ON t.TEMPLATE_ID = a.TEMPLATE_ID where a.TASK_ID = '" + taskId
                + "' AND " + "a.ITEM_ID = '" + itemId + "'";

        return query(sql);

    }

    public JSONArray mobileQuery(ReqBean reqBean) {
        // String taskType = reqBean.getInfo("taskType");
        // String queryParam = reqBean.getInfo("queryParam");
        // if (StringUtils.isBlank(taskType)) {
        // throw new BusinessException("缺少参数!");
        // }
        // String sql = "";
        // if (Constant.CHECK_TASK_TYPE_BUS.equals(taskType)) {
        // sql =
        // "SELECT a.TASK_TITLE,a.TASK_TYPE,i.*,b.BUS_ID,b.VEHICLE_NO,b.PLATE_NO,b.LINE_NO,f.NAME,f.PATH FROM `AD_CHECK_TASK` a "
        // +
        // "LEFT JOIN AD_CHECK_TASKITEM i ON i.TASK_ID = a.TASK_ID LEFT JOIN MEDIA_BUS b ON b.BUS_ID = i.ITEM_ID "
        // +
        // "LEFT JOIN MEDIA_FILE f ON f.SOURCE_DATA_ID = CONCAT(i.TASK_ID,'@',i.ITEM_ID) "
        // + "where a.TASK_TYPE = '"
        // + taskType
        // + "' "
        // + "and (b.VEHICLE_NO LIKE '%"
        // + queryParam
        // + "%' or b.LINE_NO LIKE '%"
        // + queryParam
        // + "%' or b.PLATE_NO LIKE '%"
        // + queryParam
        // + "%') order by i.MAKE_TIME DESC";
        // } else if (Constant.CHECK_TASK_TYPE_STOP.equals(taskType)) {
        // sql =
        // "SELECT a.TASK_TITLE,a.TASK_TYPE,i.*,s.STOP_ID,s.STOP_NAME,s.ROAD_NAME,s.DIRECTION,f.NAME,f.PATH FROM `AD_CHECK_TASK` a "
        // +
        // "LEFT JOIN AD_CHECK_TASKITEM i ON i.TASK_ID = a.TASK_ID LEFT JOIN MEDIA_STOP s ON s.STOP_ID = i.ITEM_ID "
        // +
        // "LEFT JOIN MEDIA_FILE f ON f.SOURCE_DATA_ID = CONCAT(i.TASK_ID,'@',i.ITEM_ID) "
        // + "where a.TASK_TYPE = '"
        // + taskType
        // + "' "
        // + " and (s.STOP_NAME LIKE '%"
        // + queryParam
        // + "%' or s.ROAD_NAME LIKE '%" + queryParam +
        // "%') order by i.MAKE_TIME DESC";
        //
        // } else if (Constant.CHECK_TASK_TYPE_ADVERTISEMENT.equals(taskType)) {
        // sql =
        // "SELECT a.TASK_TITLE,a.TASK_TYPE,i.*,r.RESOURCE_ID,r.RESOURCE_CODE,r.RESOURCE_NAME,r.MEDIA_TYPE,t.MEDIA_NAME,f.NAME,f.PATH FROM `AD_CHECK_TASK` a "
        // +
        // "LEFT JOIN AD_CHECK_TASKITEM i ON i.TASK_ID = a.TASK_ID LEFT JOIN MEDIA_RESOURCE r ON r.RESOURCE_ID = substring_index(i.ITEM_ID,'@',-1) "
        // +
        // "LEFT JOIN MEDIA_FILE f ON f.SOURCE_DATA_ID = CONCAT(i.TASK_ID,'@',i.ITEM_ID) "
        // + "LEFT JOIN MEDIA_TYPE t ON t.MEDIA_ID = r.MEDIA_TYPE "
        // + "where a.TASK_TYPE = '"
        // + taskType
        // + "' "
        // + " and (r.RESOURCE_NAME LIKE '%"
        // + queryParam
        // + "%' or r.RESOURCE_CODE LIKE '%"
        // + queryParam + "%' or t.MEDIA_NAME like '%" + queryParam +
        // "%') order by i.MAKE_TIME DESC";
        // }
        //
        // return queryTable(new SqlBuilder(sql),
        // reqBean.getInfoInt("_pageSize"), reqBean.getInfoInt("_pageIndex"));

        String taskType = reqBean.getInfo("taskType");
        if (StringUtils.isBlank(taskType)) {
            throw new BusinessException("缺少参数!");
        }

        String sql = "SELECT a.*,a.TASK_ID id,a.TASK_TITLE value, "
                + "((SELECT COUNT(1) FROM AD_CHECK_TASKITEM WHERE TASK_ID = a.TASK_ID AND STATUS = '"
                + Constant.CHECK_TASK_ITEM_STATUS_COMPLETE
                + "') / (SELECT COUNT(1) FROM AD_CHECK_TASKITEM WHERE TASK_ID = a.TASK_ID)) percent "
                + " FROM `AD_CHECK_TASK` a where a.`STATUS` = '" + CheckTask.CHECK_TASK_STATUS_PUBLISHED
                + "' AND a.TASK_TYPE = '" + taskType + "' order by a.PUBLISH_TIME DESC";

        return query(sql);
    }

    /**
     * 手机端检查任务详情
     * 
     * @param reqBean
     * @return
     */
    public JSONArray mobileCheckDetail(ReqBean reqBean) {

        String itemId = reqBean.getInfo("itemId");
        String taskId = reqBean.getInfo("taskId");

        if (StringUtils.isBlank(itemId) || StringUtils.isBlank(taskId)) {
            throw new BusinessException("缺少参数!");
        }

        String sql = "SELECT i.*,tt.TEMPLATE_ID,t.TEMPLATE_NAME,t.TEMPLATE_TYPE FROM AD_CHECK_TASKITEM i "
                + "LEFT JOIN AD_CHECK_TASKTEMPLATE tt ON tt.TASK_ID = i.TASK_ID "
                + "LEFT JOIN AD_CHECK_TEMPLATE t ON t.TEMPLATE_ID = tt.TEMPLATE_ID " + "where i.ITEM_ID = '" + itemId
                + "' AND i.TASK_ID = '" + taskId + "'";
        JSONArray itemDatas = query(sql);
        if (itemDatas != null && itemDatas.size() > 0) {
            for (int i = 0; i < itemDatas.size(); i++) {
                JSONObject data = itemDatas.getJSONObject(i);
                CheckResult checkResult = checkResultMapper.selectByPrimaryKey(data.getString("taskId"),
                        data.getString("itemId"), data.getString("templateId"));
                data.put("templateValue", checkResult.getTemplateValue());
            }
        }

        return itemDatas;
    }

    /**
     * 检查结果保存
     * 
     * @param reqBean
     */
    public void saveCheckResult(ReqBean reqBean) {

        String checkDetailStr = reqBean.getInfo("checkDetail");
        String fileStr = reqBean.getInfo("file");
        String comment = reqBean.getInfo("comment");

        JSONArray fileArr = JSONArray.fromObject(fileStr);
        JSONArray checkDetailArr = JSONArray.fromObject(checkDetailStr);
        JSONObject tempJson = null;
        if (checkDetailArr != null && checkDetailArr.size() > 0) {
            tempJson = checkDetailArr.getJSONObject(0);
            for (int i = 0; i < checkDetailArr.size(); i++) {
                JSONObject detail = checkDetailArr.getJSONObject(i);
                String taskId = detail.getString("taskId");
                String itemId = detail.getString("itemId");
                String templateId = detail.getString("templateId");
                CheckResult checkResult = checkResultMapper.selectByPrimaryKey(taskId, itemId, templateId);
                checkResult.setModifyTime(new Date());
                checkResult.setOperator(reqBean.getUserId());

                Boolean checkTrue = detail.getBoolean("checkTrue");
                if (checkTrue) {
                    checkResult.setTemplateValue("是");
                } else {
                    checkResult.setTemplateValue("否");
                }
                checkResultMapper.updateByPrimaryKeySelective(checkResult);

                // 设置item的完成时间
                TaskItem taskItem = taskItemMapper.selectByPrimaryKey(taskId, itemId);
                taskItem.setCompleteTime(new Date());
                taskItem.setAssignee(reqBean.getUserId());
                taskItemMapper.updateByPrimaryKeySelective(taskItem);
            }
        }

        if (tempJson != null) {
            String taskId = tempJson.getString("taskId");
            String itemId = tempJson.getString("itemId");
            TaskItem taskItem = taskItemMapper.selectByPrimaryKey(taskId, itemId);
            taskItem.setModifyTime(new Date());
            taskItem.setOperator(reqBean.getUserId());
            taskItem.setComment(comment);
            taskItem.setStatus(Constant.CHECK_TASK_ITEM_STATUS_COMPLETE);
            taskItemMapper.updateByPrimaryKeySelective(taskItem);

            JSONObject json = new JSONObject();
            json.put("sourceType", Constant.MEDIAFILE_SOURCE_TYPE_RESOURCEADCHECK);
            json.put("sourceDataId", taskId + "@" + itemId); // 插入广告检查记录Id
            if (fileArr != null && fileArr.size() > 0) {
                json.put("files", fileArr);
            }
            // 调用插入
            mediaFileService.addMediaFile(json);
        }
    }

    public JSONObject checkTaskItemMore(ReqBean reqBean) {
        String taskId = reqBean.getInfo("taskId");
        String itemId = reqBean.getInfo("itemId");
        if (StringUtils.isBlank(taskId) || StringUtils.isBlank(itemId)) {
            throw new BusinessException("缺少参数!");
        }
        String sql = "SELECT a.*,f.`NAME`,f.PATH,f.SOURCE_TYPE,f.SOURCE_DATA_ID FROM AD_CHECK_TASKITEM a "
                + "LEFT JOIN MEDIA_FILE f ON f.SOURCE_DATA_ID = CONCAT(a.TASK_ID,'@',a.ITEM_ID) "
                + "where a.TASK_ID = '" + taskId + "' AND a.ITEM_ID = '" + itemId + "'";

        JSONArray array = query(sql);
        JSONObject returnJson = new JSONObject();
        if (array != null && array.size() > 0) {
            returnJson = array.getJSONObject(0);
        }
        return returnJson;
    }

    public JSONObject mobileQueryItem(ReqBean reqBean) {
        String taskId = reqBean.getInfo("taskId");
        String taskType = reqBean.getInfo("taskType");
        if (StringUtils.isBlank(taskId) || StringUtils.isBlank(taskType)) {
            throw new BusinessException("缺少参数!");
        }

        String sql = "";
        if (Constant.CHECK_TASK_TYPE_BUS.equals(taskType)) {
            sql = "select i.*, b.VEHICLE_NO,b.PLATE_NO,b.LINE_NO FROM AD_CHECK_TASKITEM i "
                    + "LEFT JOIN MEDIA_BUS b ON b.BUS_ID = i.ITEM_ID where i.TASK_ID = '" + taskId
                    + "' GROUP BY i.ITEM_ID ORDER BY b.LINE_NO, b.VEHICLE_NO ASC";
        } else if (Constant.CHECK_TASK_TYPE_STOP.equals(taskType)) {
            sql = "select i.*, s.STOP_NAME,s.ROAD_NAME,s.DIRECTION,f.`NAME`,f.PATH,f.SOURCE_TYPE,f.SOURCE_DATA_ID FROM AD_CHECK_TASKITEM i "
                    + "LEFT JOIN MEDIA_FILE f ON f.SOURCE_DATA_ID = CONCAT(i.TASK_ID,'@',i.ITEM_ID) "
                    + "LEFT JOIN MEDIA_STOP s ON s.STOP_ID = i.ITEM_ID where i.TASK_ID = '"
                    + taskId
                    + "' GROUP BY i.ITEM_ID";
        } else if (Constant.CHECK_TASK_TYPE_ADVERTISEMENT.equals(taskType)) {
            sql = "select i.*, r.RESOURCE_ID,r.RESOURCE_CODE,r.RESOURCE_NAME,r.MEDIA_TYPE,t.MEDIA_NAME,f.`NAME`,f.PATH,f.SOURCE_TYPE,f.SOURCE_DATA_ID,"
                    + "r.BUS_ID,r.STOP_ID,b.VEHICLE_NO,b.PLATE_NO,b.LINE_NO,b.COMPANY_NAME,s.STOP_NAME,s.ROAD_NAME,s.DIRECTION,"
                    + "pb.BILL_ID,pb.BILL_NO,pb.CONTENT publishContent,c.CUSTOMER_NAME,CONCAT(i.PUBLISH_TIME,' 至 ',i.PUBLISH_OFF_TIME) publishTime "
                    + "FROM AD_CHECK_TASKITEM i "
                    + "LEFT JOIN MEDIA_FILE f ON f.SOURCE_DATA_ID = CONCAT(i.TASK_ID,'@',i.ITEM_ID) "
                    + "LEFT JOIN MEDIA_RESOURCE r ON r.RESOURCE_ID = substring_index(i.ITEM_ID,'@',-1)"
                    + "LEFT JOIN MEDIA_TYPE t ON t.MEDIA_ID = r.MEDIA_TYPE "
                    + "LEFT JOIN MEDIA_BUS b ON b.BUS_ID = r.BUS_ID "
                    + "LEFT JOIN MEDIA_STOP s ON s.STOP_ID = r.STOP_ID "
                    + "LEFT JOIN AD_PUBLISH_BILL pb ON pb.BILL_ID = SUBSTRING_INDEX(i.ITEM_ID,'@',1) "
                    + "LEFT JOIN AD_CUSTOMER c ON c.CUSTOMER_ID = pb.CUSTOMER_ID "
                    + " where i.TASK_ID = '"
                    + taskId
                    + "' GROUP BY i.ITEM_ID";
        }

        return queryTable(new SqlBuilder(sql), reqBean.getInfoInt("_pageSize"), reqBean.getInfoInt("_pageIndex"));
    }

    public JSONArray mobilePublishImgQuery(ReqBean reqBean) {
        String billId = reqBean.getInfo("billId");
        if (StringUtils.isBlank(billId)) {
            throw new BusinessException("缺少参数!");
        }
        String sql = "SELECT a.* FROM `MEDIA_FILE` a where a.SOURCE_DATA_ID = '" + billId + "' AND a.SOURCE_TYPE = '"
                + Constant.MEDIAFILE_SOURCE_TYPE_PUBLISH_IMG + "' AND a.STATUS = '1'";

        return query(sql);
    }

    public JSONArray checkTaskImgs(ReqBean reqBean) {
        String taskId = reqBean.getInfo("taskId");
        String itemId = reqBean.getInfo("itemId");
        if (StringUtils.isBlank(taskId) || StringUtils.isBlank(itemId)) {
            throw new BusinessException("缺少参数!");
        }
        String sql = "select f.`NAME`,f.PATH,f.SOURCE_TYPE,f.SOURCE_DATA_ID FROM MEDIA_FILE f "
                + "where f.SOURCE_DATA_ID = CONCAT('" + taskId + "','@','" + itemId + "')";
        return query(sql);
    }

}